﻿using System;
using System.Collections.Generic;
using System.Text;
using MySql.Data.MySqlClient;
using MySql.Data.Types;
using System.Diagnostics;

namespace GOA_Client
{
    public class Database
    {
        private string next;
        MySqlCommand command2, addPatientCom, editPatientCom;
        MySqlDataReader mysqlReader;
        string strSQL = "SELECT * FROM patient";

        static string strProvider = "SERVER=delanoman.dyndns.info;" +
        "DATABASE=rhc;" +
        "UID=root;" +
        "PASSWORD=usbw;";

        MySqlConnection mysqlCon = new MySqlConnection(strProvider);
        private string power, pulse, energy, distance, time, rpm, speed, sessienummer, patientid;
        public int userIDNumber { get; set; }

        public Database()
        {
            databaseInit();
        }

        private void databaseInit()
        {
            try
            {
                mysqlCon.Open();
            }
            catch (Exception er)
            {
                Debug.WriteLine("An Error Occured" + er.Message);
            }
        }

        public void addPatient(String voornaam, String achternaam, String gebdatum, String geslacht, String username, String password)
        {
            addPatientCom = new MySqlCommand("INSERT INTO  `rhc`.`patient` (`voornaam` ,`achternaam` ,`geboortedatum` ,`geslacht`, `username` ,`password`)VALUES ('" + voornaam + "',  '" + achternaam + "',  '" + gebdatum + "',  '" + geslacht + "','" + username + "',  '" + password + "');", mysqlCon);
            addPatientCom.ExecuteNonQuery();
        }

        public List<String> getPatient()
        {
            MySqlCommand mysqlCmd = new MySqlCommand("SELECT  `achternaam` FROM  `patient`;", mysqlCon);
            mysqlReader = mysqlCmd.ExecuteReader();
            List<String> list = new List<String>();
            while (mysqlReader.Read())
            {
                list.Add(mysqlReader.GetString(0));
            }
            mysqlReader.Close();
            return list;
        }

        public Tuple<string, string, string, string, string> getUser(String achternaam)
        {
            Debug.WriteLine("hahahajhakjhkaa");
            //hierin worden de user accounts opgehaald. Zodat deze eventueel kunnen worden vergeleken met reeds gebruikte user acounts.
            MySqlCommand mysqlCmd = new MySqlCommand("SELECT  `voornaam` ,  `geslacht` ,  `username`, `geboortedatum`, `ID` FROM  `patient` WHERE  `achternaam` =  '" + achternaam + "'", mysqlCon);
            mysqlReader = mysqlCmd.ExecuteReader();
            String a = "";
            String b = "";
            String c = "";
            String d = "";
            String e = "";
            int i = 0;
            while (i < 1 && mysqlReader.Read())
            {
                Debug.WriteLine(mysqlReader.GetString(0));
                a = mysqlReader.GetString(0);
                b = mysqlReader.GetString(1);
                c = mysqlReader.GetString(2);
                d = mysqlReader.GetString(3);
                e = mysqlReader.GetString(4);
                i++;
            }

            Tuple<string, string, string, string, string> tuple = new Tuple<string, string, string, string, string>(a, b, c, d, e);
            mysqlReader.Close();
            return tuple;
        }

        public void closeDB()
        {
            mysqlCon.Close();
        }

        private void writeSession()
        {
            Debug.WriteLine("");
            Debug.WriteLine("");
            Debug.WriteLine("Voer nu de sessie gegevens in, betreffend patient.");
            Debug.WriteLine("Patient ID:");
            patientid = Console.ReadLine();
            Debug.WriteLine("Sessienummer:");
            sessienummer = Console.ReadLine();
            Debug.WriteLine("Rounds per Minute");
            rpm = Console.ReadLine();
            Debug.WriteLine("Speed:");
            speed = Console.ReadLine();
            Debug.WriteLine("Distance");
            distance = Console.ReadLine();
            Debug.WriteLine("Time");
            time = Console.ReadLine();
            Debug.WriteLine("Energy");
            energy = Console.ReadLine();
            Debug.WriteLine("Pulse");
            pulse = Console.ReadLine();
            Debug.WriteLine("Power");
            power = Console.ReadLine();
            Debug.WriteLine("Nog 1 toevoegen? j/n");
            next = Console.ReadLine();

            command2 = new MySqlCommand("INSERT INTO  `rhc`.`data` (`ID` ,`sessienummer` ,`timestamp` ,`rpm` ,`speed` ,`distance` ,`time` ,`energy` ,`puls` ,`power`)VALUES ('" + patientid + "','" + sessienummer + "', CURRENT_TIMESTAMP ,'" + rpm + "', '" + speed + "',  '" + distance + "',  '" + time + "',  '" + energy + "',  '" + pulse + "',  '" + power + "');", mysqlCon);
            command2.ExecuteNonQuery();
        }

        public bool tryLogin(string username, string password)
        {
            bool accept = false;
            MySqlCommand cmd = new MySqlCommand("SELECT * FROM patient WHERE username = '" + username + "' AND password = '" + password + "';", mysqlCon);
            MySqlDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                if (reader.GetInt32(0) < 0)
                { accept = false; }
                else
                { 
                    accept = true;
                    userIDNumber = reader.GetInt32(0);
                }
            }

            cmd.Connection.Close();
            reader.Dispose();
            cmd.Dispose();
            Debug.WriteLine(accept);
            return accept;
        }

        public void editPatient(string voornaam, string achternaam, string gebdag, string geslacht, string username, string password, String id, bool passChange)
        {
            if (passChange)
            {
                editPatientCom = new MySqlCommand("UPDATE  `rhc`.`patient` SET  `voornaam` =  '" + voornaam + "', `achternaam` =  '" + achternaam + "', `geboortedatum` =  '" + gebdag + "', `geslacht` =  '" + geslacht + "',`username` =  '" + username + "',`password` =  '" + password + "' WHERE  `patient`.`ID` = " + id + ";", mysqlCon);
            }
            else
            {
                editPatientCom = new MySqlCommand("UPDATE  `rhc`.`patient` SET  `voornaam` =  '" + voornaam + "', `achternaam` =  '" + achternaam + "', `geboortedatum` =  '" + gebdag + "', `geslacht` =  '" + geslacht + "',`username` =  '" + username + "' WHERE  `patient`.`ID` = " + id + ";", mysqlCon);
            }
            editPatientCom.ExecuteNonQuery();
        }
    }
}

